﻿// 实体类
using System.Data;
using System.Data.SQLite;
public class School
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Class
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int SchoolId { get; set; }
}

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
    public int ClassId { get; set; }
}

public class Log
{
    public int Id { get; set; }
    public string OperationType { get; set; }
    public DateTime OperationTime { get; set; }
    public string Details { get; set; }
}

// 数据库操作类
public class SQLiteHelper
{
    private static string connectionString = "Data Source=school.db;Version=3;";

    public static int ExecuteNonQuery(string sql, params SQLiteParameter[] parameters)
    {
        using (var conn = new SQLiteConnection(connectionString))
        {
            conn.Open();
            using (var cmd = new SQLiteCommand(sql, conn))
            {
                cmd.Parameters.AddRange(parameters);
                return cmd.ExecuteNonQuery();
            }
        }
    }

    public static DataTable ExecuteQuery(string sql, params SQLiteParameter[] parameters)
    {
        using (var conn = new SQLiteConnection(connectionString))
        {
            conn.Open();
            using (var cmd = new SQLiteCommand(sql, conn))
            {
                cmd.Parameters.AddRange(parameters);
                using (var reader = cmd.ExecuteReader())
                {
                    DataTable dt = new DataTable();
                    dt.Load(reader);
                    return dt;
                }
            }
        }
    }
}

// 数据访问层
public class SchoolDAL
{
    public static void AddSchool(School school)
    {
        string sql = "INSERT INTO Schools (Name) VALUES (@Name)";
        SQLiteParameter[] parameters = {
            new SQLiteParameter("@Name", school.Name)
        };
        SQLiteHelper.ExecuteNonQuery(sql, parameters);
    }
    // 其他CRUD方法类似...
}

// Log记录
public class LogDAL
{
    public static void AddLog(string operationType, string details)
    {
        string sql = @"INSERT INTO Logs (OperationType, OperationTime, Details) 
                       VALUES (@Type, @Time, @Details)";
        SQLiteParameter[] parameters = {
            new SQLiteParameter("@Type", operationType),
            new SQLiteParameter("@Time", DateTime.Now),
            new SQLiteParameter("@Details", details)
        };
        SQLiteHelper.ExecuteNonQuery(sql, parameters);
    }

    public static DataTable GetLogs()
    {
        string sql = "SELECT * FROM Logs ORDER BY OperationTime DESC";
        return SQLiteHelper.ExecuteQuery(sql);
    }
}

// 数据库初始化
public class DbInitializer
{
    public static void Initialize()
    {
        string sql = @"
            CREATE TABLE IF NOT EXISTS Schools (
                Id INTEGER PRIMARY KEY AUTOINCREMENT,
                Name TEXT NOT NULL
            );
            
            CREATE TABLE IF NOT EXISTS Classes (
                Id INTEGER PRIMARY KEY AUTOINCREMENT,
                Name TEXT NOT NULL,
                SchoolId INTEGER REFERENCES Schools(Id)
            );
            
            CREATE TABLE IF NOT EXISTS Students (
                Id INTEGER PRIMARY KEY AUTOINCREMENT,
                Name TEXT NOT NULL,
                Age INTEGER,
                ClassId INTEGER REFERENCES Classes(Id)
            );
            
            CREATE TABLE IF NOT EXISTS Logs (
                Id INTEGER PRIMARY KEY AUTOINCREMENT,
                OperationType TEXT,
                OperationTime DATETIME,
                Details TEXT
            );";
        SQLiteHelper.ExecuteNonQuery(sql);
    }
}

// 使用示例
class Program
{
    static void Main(string[] args)
    {
        DbInitializer.Initialize();

        // 添加学校
        var school = new School { Name = "第一中学" };
        SchoolDAL.AddSchool(school);
        LogDAL.AddLog("Add", $"Added school: {school.Name}");

        // 其他操作类似...
    }
}